package com.lei.dao.impl;

import com.lei.dao.DeptDao;
import com.lei.entity.Dept;
import com.lei.util.BaseDb;
import com.lei.util.PageBean;
import com.lei.util.TransBean;

import java.util.List;
import java.util.Map;

/**
 * @author Lei Chu
 * @Date 2020/10/16 21:28
 */
public class DeptDaoImpl implements DeptDao {

    @Override
    public void selectAllDept(PageBean<Dept> page) {
        String sql = "select count(*) c from t_dept";
        List<Map> count = BaseDb.query(sql);
        //给page对象赋值：从数据库查出来的总条数
        page.setCount((Long) count.get(0).get("c"));

        sql = "SELECT\n" +
                "\tt.deptid,\n" +
                "\tt.deptNo,\n" +
                "\tt.dname,\n" +
                "\tt.remark,\n" +
                "\tt.status,\n" +
                "\tcount \n" +
                "FROM\n" +
                "\tt_dept t \n" +
                "\tLEFT JOIN ( SELECT s.deptid, count(*) count FROM t_staff s where s.status=1 GROUP BY s.deptid ) AS rc ON rc.deptid = t.deptid  limit ?,? ";
        List<Map> maps = BaseDb.query(sql, (page.getPage() - 1) * page.getLimit(), page.getLimit());
        if (maps.size() > 0) {
            // 给page对象赋值，从数据库产出来的所有部门信息
            page.setData(TransBean.populate(Dept.class, maps));
        }
    }

    @Override
    public int findIDDeptByName(String dname) {
        String sql = "select deptid from t_dept where dname=? ";
        List<Map> maps = BaseDb.query(sql, dname);
        // 如果根据名字查询到了，则返回这个名字的deptid主键
        if (maps.size() > 0) {
            return (int) maps.get(0).get("deptid");
        }
        return -1;
    }

    @Override
    public int addDept(Dept dept) {
        String sql = "insert into t_dept (deptid,deptNo,dname,remark,status) values (null,?,?,?,1)";
        return BaseDb.update(sql, dept.getDeptNo(), dept.getDname(), dept.getRemark());
    }

    @Override
    public void selectAllDeptById(PageBean page) {
        String sql = "select count(*) c from t_dept where deptNo like ? ";
        List<Map> count = BaseDb.query(sql, '%' + page.getParams() + '%');
        if (!count.isEmpty()) {
            //设置数据总条数
            page.setCount((Long) count.get(0).get("c"));
            sql = "SELECT\n" +
                    "\tt.deptid,\n" +
                    "\tt.deptNo,\n" +
                    "\tt.dname,\n" +
                    "\tt.status,\n" +
                    "\tt.remark,\n" +
                    "\tcount \n" +
                    "FROM\n" +
                    "\tt_dept t \n" +
                    "\tLEFT JOIN ( SELECT s.deptid, count(*) count FROM t_staff s where s.status=1 GROUP BY s.deptid ) AS rc ON rc.deptid = t.deptid  where deptNo like ? limit ?,? ";
            List<Map> list = BaseDb.query(sql, '%' + page.getParams() + '%', (page.getPage() - 1) * page.getLimit(), page.getLimit());
            if (!list.isEmpty()) {
                //将查询到的数据装填到page的data中
                page.setData(TransBean.populate(Dept.class, list));
            }
        }
    }

    @Override
    public void selectAllDeptByName(PageBean page) {
        String sql = "select count(*) c from t_dept where dname like ? ";
        List<Map> count = BaseDb.query(sql, '%' + page.getParams() + '%');
        if (!count.isEmpty()) {
            //设置数据总条数
            page.setCount((Long) count.get(0).get("c"));
            sql = "SELECT\n" +
                    "\tt.deptid,\n" +
                    "\tt.deptNo,\n" +
                    "\tt.dname,\n" +
                    "\tt.remark,\n" +
                    "\tt.status,\n" +
                    "\tcount \n" +
                    "FROM\n" +
                    "\tt_dept t \n" +
                    "\tLEFT JOIN ( SELECT s.deptid, count(*) count FROM t_staff s where s.status=1 GROUP BY s.deptid ) AS rc ON rc.deptid = t.deptid  where dname like ? limit ?,? ";
            List<Map> list = BaseDb.query(sql, '%' + page.getParams() + '%', (page.getPage() - 1) * page.getLimit(), page.getLimit());
            if (!list.isEmpty()) {
                //将查询到的数据装填到page的data中
                page.setData(TransBean.populate(Dept.class, list));
            }
        }
    }

    @Override
    public int selectLastDeptNo() {
        String sql = "select deptNo from t_dept order by deptNo desc limit 1";
        List<Map> list = BaseDb.query(sql);
        if (!list.isEmpty()) {
            return (int) list.get(0).get("deptNo");
        }
        return 0;
    }

    @Override
    public int updateDept(Dept dept) {
        String sql = "update t_dept set dname=?,remark=? where deptid = ?";
        return BaseDb.update(sql,dept.getDname(),dept.getRemark(),dept.getDeptid());
    }

    @Override
    public List<Dept> getAllDept() {
        String sql = "select deptid,dname from t_dept where status = 1";
        return TransBean.populate(Dept.class,BaseDb.query(sql));
    }

    @Override
    public int deleteDept(int deptid) {
        String sql = "delete from t_dept where deptid = ?";
        return  BaseDb.update(sql,deptid);
    }

    @Override
    public int updateDeptStatus(int deptid, int status) {
        String sql = "update t_dept set status = ? where deptid = ? ";
        if (status == 0){
          return BaseDb.update(sql,1,deptid);
        }else{
            return BaseDb.update(sql,0,deptid);
        }

    }

}
